﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;
using System.Collections;


namespace RLZY.others
{
    class safe
    {
        /// <summary>
        /// 工资保护
        /// </summary>
        public SqlDataReader GetGZSafe(String date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select  是否保护 from GZ  where  是否保护='是'  and substring(日期,1,7)=@日期 ");
            //strSql.Append("   and substring(日期,6,2)=@日期2 ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@日期", date.Substring(0,7)),
            };
            return DbHelperSQL.ExecuteReader(strSql.ToString(), param);

        }
        /// <summary>
        /// 工资初始化和保护
        /// </summary>
        public int GZOpreation(String qx,String date,String date2)
        {
            List<String> list = new List<string>();
            list.Add("  select  [日期], [部门单位], [车间班组], [姓名], [性别], [技能工资], [岗位工资], [绩效工资], [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费], [车贴], [星级津贴], [津贴1], [津贴2], [津贴3], [津贴4], [津贴5], [五项津贴], [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款], [应发总额], [应发], [扣失业保险], [扣养老金], [扣医疗保险], [扣公积金], [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费], [扣款1], [扣款2], [扣款3], [扣款4], [扣款5], [五项扣款], [实发金额], [实发], [帐号], [技能工资级别], [岗位工资类别], [岗位名称], [参加工作时间], [身份证号], [用工性质], [备注], [部门单位备注], [工号], [老工号], [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], [人数], [是否保护], [行车工津贴2],星级津贴2, [tmp] into GZTmp from gz  where  部门单位  like '%'+'" + qx + "'+'%' and substring(日期,1,7)='" + date.Substring(0, 7) + "'  and 是否保护='否' ");
            list.Add(" update GZTmp set [日班]=0 ");
            list.Add(" update GZTmp set [中班]=0  ");
            list.Add(" update GZTmp set [晚班]=0 ");
            list.Add(" update GZTmp set [大晚班]=0 ");
            list.Add(" update GZTmp set [中晚班费]=0 ");
            list.Add(" update GZTmp set [加班]=0  ");
            list.Add(" update GZTmp set [加班工资]=0  ");
            list.Add("  update GZTmp set [补发]=0 ");
            list.Add(" update GZTmp set [事假]=0  ");
            list.Add(" update GZTmp set [扣事假工资]=0 ");
            list.Add(" update GZTmp set [病假]=0 ");
            list.Add(" update GZTmp set [扣病假工资]=0 ");
            list.Add(" update GZTmp set [公假]=0   ");
            list.Add(" update GZTmp set [工伤]=0   ");
            list.Add(" update GZTmp set [产假]=0 ");
            list.Add(" update GZTmp set [其它扣款]=0  ");
            list.Add(" update GZTmp set [应发总额]=0 ");
            list.Add(" update GZTmp set [实发金额]=0 ");
            list.Add(" update GZTmp set [应发]=0  ");
            list.Add(" update GZTmp set [实发]=0   ");
            list.Add(" update GZTmp set [人数]=1  ");
            list.Add(" update GZTmp set 是否保护='否' ");
            list.Add(" update GZTmp set 日期='" + date2 + "'  ");
            list.Add(" update GZTmp set 行车工津贴=行车工津贴2  ");
            list.Add(" update GZTmp set 星级津贴=星级津贴2 ");
            list.Add(" update GZTmp set [扣款4]=0  ");
            list.Add(" update GZTmp set [扣款5]=0   ");
            list.Add(" insert into  GZ([日期], [部门单位], [车间班组], [姓名], [性别], [技能工资], [岗位工资], [绩效工资], [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费], [车贴], [星级津贴], [津贴1], [津贴2], [津贴3], [津贴4], [津贴5], [五项津贴], [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款], [应发总额], [应发], [扣失业保险], [扣养老金], [扣医疗保险], [扣公积金], [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费], [扣款1], [扣款2], [扣款3], [扣款4], [扣款5], [五项扣款], [实发金额], [实发], [帐号], [技能工资级别], [岗位工资类别], [岗位名称], [参加工作时间], [身份证号], [用工性质], [备注], [部门单位备注], [工号], [老工号], [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], [人数], [是否保护], [行车工津贴2],星级津贴2, [tmp])  select  [日期], [部门单位], [车间班组], [姓名], [性别], [技能工资], [岗位工资], [绩效工资], [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费], [车贴], [星级津贴], [津贴1], [津贴2], [津贴3], [津贴4], [津贴5], [五项津贴], [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款], [应发总额], [应发], [扣失业保险], [扣养老金], [扣医疗保险], [扣公积金], [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费], [扣款1], [扣款2], [扣款3], [扣款4], [扣款5], [五项扣款], [实发金额], [实发], [帐号], [技能工资级别], [岗位工资类别], [岗位名称], [参加工作时间], [身份证号], [用工性质], [备注], [部门单位备注], [工号], [老工号], [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], [人数], [是否保护], [行车工津贴2],星级津贴2, [tmp] from  GZTmp     ");
            list.Add("  if  exists (select name from sysobjects where name='GZTmp2')  drop table GZTmp2 ");
            list.Add(" select     [姓名],  [公积金号], 用工性质, [部门单位], 扣公积金*2 as 公积金入帐 ,[日期]  into GZTmp2  from gz  where  部门单位 like '%'+'" + qx + "'+'%' and substring(日期,1,7)='" + date.Substring(0, 7) + "' and 是否保护='否' and not(公积金号 is null or 公积金号=' ')  ");
            list.Add(" insert into  GJJ_RZ  select  [姓名], [公积金号], [用工性质], [部门单位], [公积金入帐], [日期] from  GZTmp2     ");
            list.Add(" update GZ   set  是否保护='是'  where  substring(日期,1,7)<='" + date.Substring(0, 7) + "' ");
            list.Add(" if  exists (select name from sysobjects where name='GZTmp')  drop table GZTmp ");
            list.Add(" if  exists (select name from sysobjects where name='GZTmp2')  drop table GZTmp2 ");
            list.Add(" if  exists (select name from sysobjects where name='GZTmp3')  drop table GZTmp3 ");
    
            return DbHelperSQL.ExecuteSqlTran(list);
        }

        /// <summary>
        /// 工龄津贴保护
        /// </summary>
        public SqlDataReader GetDateSafe(String time)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select  是否保护 from GZ  where 是否保护='是' and  substring(日期,1,4)=@日期1 ");
            strSql.Append("   and substring(日期,6,2)=@日期2 ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@日期1", time.Substring(0, 4)),
                new SqlParameter("@日期2", time.Substring(5, 2)),
                

            };

            return DbHelperSQL.ExecuteReader(strSql.ToString(), param);

        }
        /// <summary>
        /// 初始化NULL值为0
        /// </summary>
        public int set0TONull()
        { 
             List<String> list = new List<string>();
         	list.Add( "update GZ set [技能工资]=0 where [技能工资] is Null  ");
	        list.Add( "update GZ set [岗位工资]=0 where [岗位工资] is Null  ");
	        list.Add( "update GZ set [绩效工资]=0 where [绩效工资] is Null  ");
	        list.Add( "update GZ set [粮贴]=0 where [粮贴] is Null  ");
	        list.Add( "update GZ set [洗理费]=0 where [洗理费] is Null  ");
	        list.Add( "update GZ set [水电气补贴]=0 where [水电气补贴] is Null  ");
	        list.Add( "update GZ set [三项补贴]=0 where [三项补贴] is Null  ");
	        list.Add( "update GZ set [回贴]=0 where [回贴] is Null  ");
	        list.Add( "update GZ set [工龄津贴]=0 where [工龄津贴] is Null  ");
	        list.Add( "update GZ set [职称津贴]=0 where [职称津贴] is Null  ");
	        list.Add( "update GZ set [特岗津贴]=0 where [特岗津贴] is Null  ");
	        list.Add( "update GZ set [行车工津贴]=0 where [行车工津贴] is Null  ");
	        list.Add( "update GZ set [卫生纸费]=0 where [卫生纸费] is Null  ");
	        list.Add( "update GZ set [日班]=0 where [日班] is Null  ");
	        list.Add( "update GZ set [中班]=0 where [中班] is Null  ");
	        list.Add( "update GZ set [晚班]=0 where [晚班] is Null  ");
	        list.Add( "update GZ set [大晚班]=0 where [大晚班] is Null  ");
	        list.Add( "update GZ set [中晚班费]=0 where [中晚班费] is Null  ");
	        list.Add( "update GZ set [加班]=0 where [加班] is Null  ");
	        list.Add( "update GZ set [加班工资]=0 where [加班工资] is Null  ");
	        list.Add( "update GZ set [补发]=0 where [补发] is Null  ");
	        list.Add( "update GZ set [事假]=0 where [事假] is Null  ");
	        list.Add( "update GZ set [扣事假工资]=0 where [扣事假工资] is Null  ");
	        list.Add( "update GZ set [病假]=0 where [病假] is Null  ");
	        list.Add( "update GZ set [扣病假工资]=0 where [扣病假工资] is Null  ");
	        list.Add( "update GZ set [公假]=0 where [公假] is Null  ");
	        list.Add( "update GZ set [工伤]=0 where [工伤] is Null  ");
	        list.Add( "update GZ set [产假]=0 where [产假] is Null  ");
	        list.Add( "update GZ set [其它扣款]=0 where [其它扣款] is Null  ");
	        list.Add( "update GZ set [应发总额]=0 where [应发总额] is Null  ");
	        list.Add( "update GZ set [扣失业保险]=0 where [扣失业保险] is Null  ");
	        list.Add( "update GZ set [扣养老金]=0 where [扣养老金] is Null  ");
	        list.Add( "update GZ set [扣医疗保险]=0 where [扣医疗保险] is Null  ");
	        list.Add( "update GZ set [扣公积金]=0 where [扣公积金] is Null  ");
	        list.Add( "update GZ set [扣电视费]=0 where [扣电视费] is Null  ");
	        list.Add( "update GZ set [扣治安费]=0 where [扣治安费] is Null  ");
	        list.Add( "update GZ set [扣会费]=0 where [扣会费] is Null  ");
	        list.Add( "update GZ set [扣互助金]=0 where [扣互助金] is Null  ");
	        list.Add( "update GZ set [扣丧葬费]=0 where [扣丧葬费] is Null  ");
	        list.Add( "update GZ set [实发金额]=0 where [实发金额] is Null  ");
            return DbHelperSQL.ExecuteSqlTran(list);
        
        }
        /// <summary>
        /// 调整工龄津贴
        /// </summary>
        public int modify(String date)
        {
            List<String> list = new List<string>();
            list.Add("  update GZ set 工龄津贴=3*(datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1)   where datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1>10  and  datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1<=15  and   substring(日期,1,4)='" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否' ");
            list.Add("  update GZ set 工龄津贴=4*(datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1)   where datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1>15  and  datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1<=20  and   substring(日期,1,4)='" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否' ");
            list.Add("  update GZ set 工龄津贴=5*(datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1)   where datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1>20  and  datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1<=25  and   substring(日期,1,4)='" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否' ");
            list.Add("  update GZ set 工龄津贴=6*(datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1)   where datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1>25  and  datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1<=30  and   substring(日期,1,4)='" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否' ");
            list.Add("  update GZ set 工龄津贴=7*(datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1)   where datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1>30  and  datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1<=35  and   substring(日期,1,4)='" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否' ");
            list.Add("  update GZ set 工龄津贴=8*(datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1)   where datediff(yy,convert(datetime,参加工作时间),convert(datetime,日期))+1>35   and   substring(日期,1,4)='" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否' ");
            list.Add(" update GZ set 应发总额 =技能工资+岗位工资+绩效工资+粮贴+回贴+洗理费+水电气补贴+工龄津贴+职称津贴+特岗津贴+行车工津贴+卫生纸费+车贴+星级津贴+津贴1+津贴2+津贴3+津贴4+津贴5+补发+中晚班费+加班工资-扣病假工资-扣事假工资-其它扣款   where substring(日期,1,4)='" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否'  ");
            list.Add("  update GZ set 实发金额=应发总额-扣会费-扣丧葬费-扣互助金-扣治安费-扣电视费-扣养老金-扣失业保险-扣医疗保险-扣公积金-扣款1-扣款2-扣款3-扣款4-扣款5  where substring(日期,1,4)='" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否'   ");
            list.Add(" update GZ set 应发 =技能工资+岗位工资+绩效工资+粮贴+回贴+洗理费+水电气补贴+工龄津贴+职称津贴+特岗津贴+卫生纸费+补发+中晚班费+加班工资-扣病假工资-扣事假工资-其它扣款  where substring(日期,1,4)= '" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否'  ");
            list.Add(" update GZ set 实发=应发-扣会费-扣丧葬费-扣互助金-扣治安费-扣电视费-扣养老金-扣失业保险-扣医疗保险-扣公积金-扣款5  where substring(日期,1,4)='" + date.Substring(0, 4) + "'  and substring(日期,6,2)='" + date.Substring(5, 2) + "'  and 是否保护='否'  ");
            list.Add(" update GZ set 其它扣款=其它扣款 + 实发金额   where   (部门单位='锻造厂' or (部门单位='特钢机关'and 车间班组='驻外')) and 是否保护='否'   ");
            list.Add(" update GZ set 应发总额 =技能工资+岗位工资+绩效工资+粮贴+回贴+洗理费+水电气补贴+工龄津贴+职称津贴+特岗津贴+行车工津贴+卫生纸费+车贴+星级津贴+津贴1+津贴2+津贴3+津贴4+津贴5+补发+中晚班费+加班工资-扣病假工资-扣事假工资-其它扣款    where  (部门单位='锻造厂' or (部门单位='特钢机关'and 车间班组='驻外')) and 是否保护='否'   ");
            list.Add(" update GZ set 实发金额=应发总额-扣会费-扣丧葬费-扣互助金-扣治安费-扣电视费-扣养老金-扣失业保险-扣医疗保险-扣公积金-扣款1-扣款2-扣款3-扣款4-扣款5    where  (部门单位='锻造厂' or (部门单位='特钢机关'and 车间班组='驻外')) and 是否保护='否'  ");
            list.Add(" update GZ set 应发 =应发总额   where  (部门单位='锻造厂' or (部门单位='特钢机关'and 车间班组='驻外')) and 是否保护='否'  ");
            list.Add(" update GZ set 实发= 实发金额    where   (部门单位='锻造厂' or (部门单位='特钢机关'and 车间班组='驻外')) and 是否保护='否'  ");
            
            return DbHelperSQL.ExecuteSqlTran(list);

        }
    }
}
